/*
 * BBDBeanConnection.java
 *
 * Created on January 20, 2007, 5:02 AM
 *
 * Layer above JDBC to abstract stored procedure operations.
 *

 *
 */

package bbd;

/*
 * Copyright 2007 microneering, Inc and James Gamber
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 * 
 * BBD/bbd/BBDBeanConnection.java
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.Timer;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * This loads the driver and creates a connection.
 * 
 * This encapsulates the JDBC API and transforms the results to objects that are
 * independant of the JDBC and the database schema.
 * 
 * 
 * @param B
 *            any POJO bean
 * @param L
 *            lists of beans
 * @author james gamber
 */
public class BBDBeanConnection<B, L extends ArrayList<B>> {

	/**
	 * String value for boolean 'true'.
	 */
	static private final String TRUE = "true";

	/** Database JDBC driver class name */
	static private Class driverClass = null;

	/** Timer to periodically refresh cached SQL */
	static final protected Timer refreshSQL = new Timer();

	/** Database where SQL is stored */
	static final protected String BBD = BBDProperties.get(BBDProperties.BBDDatabase);

	/**
	 * Cache of SQL execute statements The saves selecting on the
	 * bbd.storedprocedure table after the first time. Caches are cleared
	 * periodically by the BBDRefreshTimer.
	 */
	static final protected Map<String, String> apiMap = Collections.synchronizedMap(new HashMap<String, String>());

	/**
	 * Cache of PreparedStatement Meta Data Using cached data saves round trips
	 * through the network to the database. Caches are cleared periodically by
	 * the BBDRefreshTimer.
	 */
	static final private Map<String, BBDParameterMetaData[]> pmdMap = Collections.synchronizedMap(new HashMap<String, BBDParameterMetaData[]>());

	/** Refresh time period in milliseconds */
	static final protected long refreshPeriod = new Long(BBDProperties.get(BBDProperties.RefreshCachePeriod)); // one

	// day

	/** Logger */
	static private final Logger log = Logger.getLogger(BBDBeanConnection.class.getName());

	static {
		log.setLevel(Level.WARNING);
	}

	/**
	 * Principal used to access the BBD database tables
	 * 
	 * Package private to use in the BBD layer
	 */
	static final BBDAPIPrincipal bbdDBPrincipal = new BBDAPIPrincipal(BBDProperties.get(BBDProperties.BBDUser), BBDProperties.get(BBDProperties.BBDPassword));

	/* Use BBD Principal for all database access when no [principal is specified */
	static private final boolean bbdPrincipalIsDefault = TRUE.equalsIgnoreCase(BBDProperties.get(BBDProperties.BBDPrincipalIsDefault)) ? Boolean.TRUE
			: Boolean.FALSE;

	/* Maintain open connections */
	static private final Map<BBDAPIPrincipal, Connection> conns = Collections.synchronizedMap(new HashMap<BBDAPIPrincipal, Connection>());

	/* Clear cache data */
	static void clear() {
		apiMap.clear();
		pmdMap.clear();
	}

	/** User Principal for Connection to database */
	private BBDAPIPrincipal currentPrinc = null;

	/** Creates a new instance of MyConnection */
	public BBDBeanConnection() {
	}

	/**
	 * Used in this class to get a connection.
	 * 
	 * This class is single threaded and contains one connection per instance.
	 * 
	 * These connections are expensive, but in app servers (etc), a datasource
	 * and a connection pool would be used.
	 * 
	 * @param principal
	 *            used to connect to database
	 * @return JDBC Connection or throw exception if connection fails
	 * @throws java.sql.SQLException
	 */
	protected Connection getConnection(final BBDAPIPrincipal principal) throws SQLException {

		if (principal == null) {
			final SQLException e = new SQLException("specific database connection logon is required, but not supplied");
			e.fillInStackTrace();
			final StackTraceElement[] ste = e.getStackTrace();
			final StringBuffer sb = new StringBuffer("\n");
			for (final StackTraceElement st : ste) {
				sb.append(st.toString());
				sb.append("\n");
			}
			log.severe(e.getMessage() + "\n" + sb.toString());
			throw e;
		}

		Connection con = conns.get(principal);

		if (con != null) {

			if (con == conns.get(bbdDBPrincipal)) {
				return con;
			}

			if (currentPrinc == null) {
				currentPrinc = principal;
				principal.incrementUsage();
			}
			return con;
		}

		if (driverClass == null) {

			try {

				driverClass = Class.forName(BBDProperties.get(BBDProperties.DriverClass));

			} catch (final java.lang.ClassNotFoundException e) {

				log.severe(e.getMessage());
				throw new SQLException("Driver class not found " + BBDProperties.get(BBDProperties.DriverClass));
			}

			/**
			 * Use a timer to refesh the info in memory to reflect changes to
			 * the changes in the database. Only need to created this as a
			 * static singleton for all objects of BBDConnection.
			 * 
			 * This small amount of data is cached because it is used often and
			 * caching saves a lot traffic to the database. Caching is a
			 * performance optimization.
			 * 
			 * The refresh period is set in the properties file.
			 * 
			 */
			refreshSQL.scheduleAtFixedRate(new BBDRefreshTimerTask(), refreshPeriod, refreshPeriod);

		}

		try {

			String connectionString = BBDProperties.get(BBDProperties.BBDConnectionString);
			if (principal != null && !principal.equals(bbdDBPrincipal) && !bbdPrincipalIsDefault) {
				connectionString = BBDProperties.get(BBDProperties.UserConnectionString);
			}
			connectionString = String.format(connectionString, principal.getName(), principal.getPassword());
			con = DriverManager.getConnection(connectionString);
			conns.put(principal, con);
			currentPrinc = principal;

		} catch (final SQLException ex) {
			log.severe(ex.getMessage());
			throw ex;
		}

		return con;
	}

	/**
	 * Execute the specified black box database API.
	 * 
	 * @param storedProcedure
	 *            BBDAPI contains storedProcure name, database, and deprecated
	 *            status.
	 * @param params
	 *            optional parameters to place in stored procedure call that
	 *            contains '?'. This is used with parameterized SQL suce sas
	 *            call HelloWord2("Duke");
	 * @return List an Array List of rows from the result set of the query.
	 * @throws java.sql.SQLException
	 *             JDBC thrown exception
	 */
	public BBDBeanArrayList<B> executeQuery(final BBDBeanAPI<B> storedProcedure, final Object... params) throws SQLException {

		ResultSet rs = null;
		Statement stmt = null;
		BBDBeanArrayList<B> sqlAL = new BBDBeanArrayList<B>();

		try {
			if (params.length == 0) {

				stmt = getConnection(storedProcedure.getBbdPrincipal()).createStatement();

				rs = stmt.executeQuery(getSQL(storedProcedure));

			} else {

				final PreparedStatement pstmt = getConnection(storedProcedure.getBbdPrincipal()).prepareStatement(getSQL(storedProcedure));
				insertArguments(storedProcedure, params, pstmt);
				rs = pstmt.executeQuery();

			}

			sqlAL = new BBDBeanArrayList<B>(rs, storedProcedure);

		} finally {

			if (stmt != null)
				stmt.close();

			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Throwable e) {
			}
		}

		return sqlAL;
	}

	/**
	 * Used to insert, update, or delete rows in the database.
	 * 
	 * @param storedProcedure
	 *            BBDAPI contains storedProcure name, database, and deprecated
	 *            status.
	 * @param params
	 *            Variable argument list of substitution values. One value for
	 *            each '?' in the SQL.
	 * @throws java.sql.SQLException
	 *             JDBC thrown exception
	 * @return integer value of number of rows updated.
	 */
	public int executeUpdate(final BBDBeanAPI<B> storedProcedure, final Object... params) throws SQLException {

		int rowsUpdated = 0;
		Statement stmt = null;
		PreparedStatement pstmt = null;

		try {
			if (params.length == 0) {
				/**
				 * In this case the entire update is in the stored procedure
				 * with no data being passed into the stored procedure. An
				 * example of this might be resetting all date values in a
				 * table.
				 */
				stmt = getConnection(storedProcedure.getBbdPrincipal()).createStatement();
				rowsUpdated = stmt.executeUpdate(getSQL(storedProcedure));

			} else {

				pstmt = getConnection(storedProcedure.getBbdPrincipal()).prepareStatement(getSQL(storedProcedure));

				insertArguments(storedProcedure, params, pstmt);
				rowsUpdated = pstmt.executeUpdate();

			}
		} finally {

			if (stmt != null)
				stmt.close();
		}

		return rowsUpdated;

	}

	/**
	 * Arguments passed in are substituted in the SQL. Every place the SQL has a
	 * '?' a value from params is substituted.
	 * 
	 * If the database supports getParameterMetaData, then the data is validated
	 * by type. For exammple, if a Integer is passed into as param, but the
	 * field uses a TinyInt type, and the Integer is greater than the largest
	 * possible TinyInt, an exception is thrown.
	 * 
	 * @param api
	 *            BBDAPI contains db and sp name.
	 * @param params
	 *            arguments to substitute into the SQL.
	 * @param pstmt
	 *            a JDBC prepared statement.
	 * 
	 * @throws java.sql.SQLException
	 */
	protected void insertArguments(final BBDBeanAPI<B> api, final Object params[], final PreparedStatement pstmt) throws SQLException {

		String pmdKey = api.getDatabaseUsed() + ":" + api.getStoredProcedureName();
		BBDParameterMetaData[] bbdPmd = pmdMap.get(pmdKey);

		if (bbdPmd == null) {

			/* if the parameter meta data is not cached, create it and cache it. */
			ParameterMetaData pmd = pstmt.getParameterMetaData();
			bbdPmd = new BBDParameterMetaData[pmd.getParameterCount()];
			if (pmd.getParameterCount() != params.length) {
				String errorMsg = "API requires " + pmd.getParameterCount() + ", but only " + params.length + " parameters where passed!";
				log.severe(errorMsg);
				throw new IllegalArgumentException(errorMsg);
			}

			int paramIndex = 0;
			for (int index = 1; index <= pmd.getParameterCount(); index++) {

				final int mode = pmd.getParameterMode(index);
				int type = BBDValidator.UNKNOWNTYPE;
				final String name = "api argument " + index;
				try {
					type = pmd.getParameterType(index);
				} catch (final Exception e) {
					// attempt to set type based on class name,
					// TODO expand this list to cover all required db types
					if (params[paramIndex].getClass().getName().contains("String")) {
						type = Types.VARCHAR;
					} else if (params[paramIndex].getClass().getName().contains("Integer")) {
						type = Types.INTEGER;
					} else if (params[paramIndex].getClass().getName().contains("Date")) {
						type = Types.DATE;
					} else if (params[paramIndex].getClass().getName().contains("TimeStamp")) {
						type = Types.TIMESTAMP;
					} else if (params[paramIndex].getClass().getName().contains("Time")) {
						type = Types.TIME;
					} else if (params[paramIndex].getClass().getName().contains("Boolean")) {
						type = Types.BOOLEAN;
					}
				}

				BBDParameterMetaData bbdPMD = new BBDParameterMetaData(mode, name, type);
				bbdPmd[paramIndex++] = bbdPMD;
			}

			pmdMap.put(pmdKey, bbdPmd);
		}

		int paramIndex = 0;
		for (int index = 0; index < bbdPmd.length; index++) {

			BBDParameterMetaData bbdPMD = bbdPmd[index];
			final int mode = bbdPMD.getMode();

			if (mode == ParameterMetaData.parameterModeIn || mode == ParameterMetaData.parameterModeInOut) {
				int type = bbdPMD.getType();

				// MySQL jdbc driver currently does not return the type of each
				// argument, so no validation can be done here
				if (type != BBDValidator.UNKNOWNTYPE) {

					final String columnName = bbdPMD.getName();

					if (!BBDValidator.isValidSqlData(params[paramIndex], type, columnName)) {
						final String msg = "Invalid data [" + params[paramIndex] + "] at " + paramIndex;
						log.severe(msg);
						throw new SQLException(msg);
					}
				} else {
					log.warning("Unknown SQL data type was not validated, paramater " + paramIndex);
				}
				pstmt.setObject(index + 1, params[paramIndex]);

				paramIndex++;
			}
		}
	}

	/**
	 * This finds the execute SQL by stored procedure name.
	 * 
	 * Store procedure call statements are stored in the BBD database
	 * StoreProcedure table.
	 * 
	 * After finding the SQL, it is stored in a map so the next time is it
	 * needed, it comes from memory rather than the database.
	 * 
	 * @param A
	 *            stored Procedure API
	 * 
	 * @return String containing the SQL to execute the stored procedure.
	 * 
	 * @throws SQLExcption
	 */
	protected <A extends BBDBeanAPI> String getSQL(final A storedProcedure) throws SQLException {

		/**
		 * First attempt to find the storedProcedureName in the hashmap. This is
		 * faster than checking the database.
		 */
		String sql = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int row = 0;

		final String apiKey = storedProcedure.getDatabaseUsed() + ":" + storedProcedure.getStoredProcedureName();

		synchronized (apiMap) {
			sql = apiMap.get(apiKey);
		}

		if (sql != null) {
			log.info("BBD API from cache for " + apiKey);
			useDatabase(storedProcedure);
			storedProcedure.setSQL(sql);
			return sql;
		}

		try {
			// Get the seed sql from the properties file
			final String getAPI = BBDProperties.get(BBDProperties.GetAPI);
			pstmt = getConnection(bbdDBPrincipal).prepareStatement(getAPI);
			pstmt.setString(1, storedProcedure.getDatabaseUsed());
			pstmt.setString(2, storedProcedure.getStoredProcedureName());

			useBBD();
			rs = pstmt.executeQuery();

			while (rs.next()) {
				storedProcedure.setBBDAPI(rs);
				sql = storedProcedure.getSQL();

				if (storedProcedure.isDeprecated()) {
					final Throwable th = new Throwable();
					th.fillInStackTrace();
					final StackTraceElement[] ste = th.getStackTrace();
					final StringBuffer sb = new StringBuffer("\n");
					for (final StackTraceElement st : ste) {
						sb.append(st.toString());
						sb.append("\n");
					}
					log.warning("BBD API from database for DEPRECATED " + apiKey + sb.toString());
				} else {
					log.info("BBD API from database for " + apiKey);
				}
				row++;
			}
		} finally {

			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Throwable e) {
			}

			if (pstmt != null)
				pstmt.close();
		}

		/**
		 * In production, disable test APIs.
		 */
		if (storedProcedure.isTestOnly() && !TRUE.equalsIgnoreCase(BBDProperties.get(BBDProperties.UseTestAPI))) {
			log.severe("API row is for test " + apiKey);
			throw new SQLException("API row is for test " + apiKey);
		}

		/**
		 * When the SQL API is not located, you may optionally accept the SQL
		 * passed in with the SQLAPI.
		 */
		boolean SQLPassThrough = false;
		if (TRUE.equalsIgnoreCase(BBDProperties.get(BBDProperties.SQLPassThrough))) {
			SQLPassThrough = true;
		}

		if (row != 1 && !SQLPassThrough) {
			log.severe("API row is missing " + apiKey);
			throw new SQLException("API row is missing for " + apiKey);
		} else if (row == 0 && SQLPassThrough) {
			sql = storedProcedure.getSQL();
			if (sql == null || sql.length() == 0) {
				log.severe("API row is missing " + apiKey);
				throw new SQLException("API row is missing for " + apiKey);
			}
		}

		// found the getAPI sql, add it to the hashMap
		if (TRUE.equalsIgnoreCase(BBDProperties.get(BBDProperties.CacheDeprecatedAPI))) {
			synchronized (apiMap) {
				apiMap.put(apiKey, sql);
			}
		}

		// set the database used by the stored procedure
		useDatabase(storedProcedure);

		return sql;

	}

	protected void useDatabase(final BBDBeanAPI api) throws SQLException {
		if (BBDValidator.isValidDatabase(api.getDatabaseUsed())) {

			Statement stmt = null;

			try {
				stmt = getConnection(api.getBbdPrincipal()).createStatement();
				stmt.execute("use " + api.getDatabaseUsed() + ";");
			} finally {

				if (stmt != null)
					stmt.close();
			}
		}
	}

	protected void useBBD() throws SQLException {

		Statement stmt = null;
		try {

			stmt = getConnection(bbdDBPrincipal).createStatement();
			stmt.execute("use " + BBD + ";");

		} finally {

			if (stmt != null)
				stmt.close();
		}

	}

	/**
	 * Close connection and clean up resources.
	 * 
	 * @throws java.lang.Throwable
	 *             Any expections during finalize.
	 */
	@Override
	/**
	 * finalize the BBDConneciton.
	 * 
	 * The db connection used by this BBDConnection is stored based on
	 * 
	 */
	protected void finalize() throws Throwable {

		if (currentPrinc != null) {

			// never close the root connection
			if (!currentPrinc.equals(bbdDBPrincipal)) {

				try {
					synchronized (conns) {
						if (currentPrinc.decrementUsage()) {
							// this was last usage, so close and remove the
							// connection
							conns.get(currentPrinc).close();
							conns.remove(currentPrinc);
							currentPrinc = null;
						}
					}
				} catch (final SQLException ex) {
				}

			}
		}

		super.finalize();
	}

}
